﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Danser_Entity.Entities;
using Danser_Control.Managers;
using Danser_Entity.Queries.Data;
using Danser_Entity.Utils;

using Danser_Control.Exceptions;

namespace Danser_Control.ManagersImpl
{
    /// <summary>
    /// implements the IDBManager interface
    /// </summary>
    public class DBManager : IDBManager
    {
        #region Members

        private readonly String connectionString = Properties.Settings.Default.DBConnectionString;
        private SqlConnection conn;

        #endregion

        /// <summary>
        /// creates a DBManager instance
        /// </summary>
        public DBManager()
        {
            conn = GetConnection();
        }

        /// <summary>
        /// Creates a connection to the SQL Server DB
        /// <throws>
        /// Exception - if failed to initialize the connection
        /// </throws>
        /// <returns>Created connection</returns>
        /// </summary>
        private SqlConnection CreateConnection()
        {
            SqlConnection sqlConn = new SqlConnection(connectionString);
            try
            {
                sqlConn.Open();
                return sqlConn;
            }
            catch (Exception e)
            {
                throw new Exception("Failed to open connection to database: " + e.ToString());
            }

        }

        /// <summary>
        /// Returns opened connection to the DB. Open a new connection if old one was closed/timeout/has problems.
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetConnection()
        {
            if (conn == null || conn.State != ConnectionState.Open)
            {
                conn = CreateConnection();
            }
            return conn;
        }

        /// <summary>
        /// destructs the instance
        /// </summary>
        ~DBManager()
        {
            //if (conn != null)
            //{
            //    try
            //    {
            //        conn.Dispose();
            //    }
            //    catch (Exception)
            //    {
            //    }
            //}
        }

        #region Add Methods
        /* Methods to add new data */
        /// <see cref="Danser_Control.Managers.IDBManager.AddUser"/>
        public void AddUser(User user)
        {
            String cmd = "EXECUTE usp_addUser '" + user.UserID + "', '" + user.Name + "', '" + user.Permission + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.AddUserQuery"/>
        public void AddUserQuery(UserQuery query)
        {
            String cmd = "EXECUTE usp_addUserQuery '" + query.Name + "', '" + query.Description + "', '" + query.QueryString + "', '" + query.UserID + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.AddValidationTemplate"/>
        public void AddValidationTemplate(ValidationTemplate template)
        {
            String mails = template.MailList;
            String cmd = "EXECUTE usp_addValidationTemplate '" + template.Name + "', '" + template.AppliedOnDefault + "', '" + mails + "', '" + template.Expression + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.AddTestInfo"/>
        public void AddTestInfo(TestInfo test)
        {
            if (GetTest(test.TestInfoIndex) != null)
            {
                throw new DanserDuplicateKeyException(
                    String.Format(ErrorMessages.DuplicateKey, new Object[] {test.TestInfoIndex}));
            }

            String cmd = "EXECUTE usp_addTestInfo '" + test.TestInfoIndex + "', '" + test.Operator + "', '" + test.Date + "', '" + test.Time + "', '" + test.TestName + "', '" + test.APType + "', '" +
                test.APIP + "', '" + test.APFWVer + "', '" + test.NICMAC + "', '" + test.NICType + "', '" + test.ProjectName + "', '" + test.OS + "', '" + test.TIC + "', '" + test.SetupName + "', '" +
                test.PlatformModel + "', '" + test.PlatformName + "', '" + test.TrafficProtocol + "', '" + test.IsConfirmed + "', '" + test.IsAnchor + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.AddTestParams"/>
        public int AddTestParams(TestParams tParams)
        {
            SqlCommand cmd = new SqlCommand("usp_addTestParams", GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@channel", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@channelwidth", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@band", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@testinfoindex", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
            cmd.Parameters["@channel"].Value = tParams.Channel;
            cmd.Parameters["@channelwidth"].Value = tParams.ChannelWidth;
            cmd.Parameters["@band"].Value = tParams.Band;
            cmd.Parameters["@testinfoindex"].Value = tParams.TestInfoIndex;
            cmd.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

            cmd.ExecuteNonQuery();
            return (int)cmd.Parameters["@ReturnValue"].Value;
        }

        /// <see cref="Danser_Control.Managers.IDBManager.AddTestResults"/>
        public void AddTestResults(TestResults results)
        {
            TestParams param = GetTestParams(results.TestInfoIndex, results.TestParamsIndex);
            if (param == null)
            {
                return;
            }
            String cmd = "EXECUTE usp_addTestResults '" + results.TestInfoIndex + "', " + results.TestParamsIndex + ", " + results.Attenuation + ", " + results.RXMin + ", " +
                results.RXAvg + ", " + results.RXMax + ", " + results.TXMin + ", " + results.TXAvg + ", " + results.TXMax + ", " + results.RSSI1 + ", " +
                results.RSSI2 + ", " + results.RSSI3;
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.AddTests"/>
        public void AddTests(List<TestResults> tests)
        {
            foreach (TestResults results in tests)
            {
                AddTestResults(results);
            }
        }

        #endregion

        #region Delete Methods
        /* Methods to remove new data */

        /// <see cref="Danser_Control.Managers.IDBManager.DeleteUser"/>
        public void DeleteUser(String UserID)
        {
            String cmd = "EXECUTE usp_deleteUser '" + UserID + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.DeleteUserQuery"/>
        public void DeleteUserQuery(int queryID)
        {
            String cmd = "EXECUTE usp_deleteUserQuery " + queryID;
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.DeleteValidationTemplate"/>
        public void DeleteValidationTemplate(int templateID)
        {
            String cmd = "EXECUTE usp_deleteValidationTemplate " + templateID;
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.DeleteTestInfo"/>
        public void DeleteTestInfo(String testInfoIndex)
        {
            String cmd = "EXECUTE usp_deleteTestInfo '" + testInfoIndex + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.DeleteTestParams"/>
        public void DeleteTestParams(String testInfoIndex, int testParamsIndex)
        {
            List<TestResults> lstParams = GetTestResults(DBConstants.TEST_RESULTS_TEST_PARAMS_INDEX + "=" + testParamsIndex);
            if (lstParams.Count > 0)
            {
                throw new Exception("Test results exist with this test params, unable to delete. Delete test results first.");
            }
            String cmd = "EXECUTE usp_deleteTestParams " + testParamsIndex + ", '" + testInfoIndex + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.DeleteTestResults"/>
        public void DeleteTestResults(String testInfoIndex, int testParamsIndex, int attenuation)
        {
            String cmd = "EXECUTE usp_deleteTestResults '" + testInfoIndex + "', " + testParamsIndex + ", " + attenuation;
            ExecSP(cmd);
        }

        #endregion

        #region Modify Methods
        /* Methods to modify existing data */

        /// <see cref="Danser_Control.Managers.IDBManager.SetUserPermission"/>
        public void SetUserPermission(String UserID, PermissionLevel newPermission) 
        {
            String cmd = "EXECUTE usp_updateUserPermission '" + UserID + "', '" + newPermission + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.ConfirmTest"/>
        public void ConfirmTest(String testInfoIndex, bool isConfirmed)
        {
            String cmd = "EXECUTE usp_confirmTest '" + testInfoIndex + "', '" + isConfirmed + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.AnchorTest"/>
        public void AnchorTest(String testInfoIndex, bool isAnchor)
        {
            String cmd = "EXECUTE usp_anchorTest '" + testInfoIndex + "', '" + isAnchor + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.ModifyTemplateDetails"/>
        public void ModifyTemplateDetails(int templateID, String MailList, bool appliedOnDefault)
        {
            String cmd = "EXECUTE usp_modifyTemplate " + templateID + ", '" + MailList + "', '" + appliedOnDefault + "'";
            ExecSP(cmd);
        }

        #endregion

        #region General Queries
        /* Query methods */

        /// <see cref="Danser_Control.Managers.IDBManager.PerformQuery"/>
        public SqlDataReader PerformQuery(String sqlQuery) 
        {
            SqlCommand command = new SqlCommand(sqlQuery, GetConnection());
            SqlDataReader reader = command.ExecuteReader();
            return reader;
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetSpecificUser"/>
        public User GetSpecificUser(String userID)
        {
            String filter = DBConstants.USERS_ID + "='" + userID + "'";
            List<User> users = GetUsers(filter);
            //assuming 1 or 0 results only
            if (users.Count == 0)
            {
                return null;
            }
            else
            {
                return users.ToArray()[0];
            }
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetTest"/>
        public TestInfo GetTest(String testInfoIndex)
        {
            String filter = DBConstants.TEST_INFO_INDEX + "='" + testInfoIndex + "'";
            List<TestInfo> tests = GetTestInfo(filter);
            //assuming 1 or 0 results only
            if (tests.Count == 0)
            {
                return null;
            }
            else
            {
                return tests.ToArray()[0];
            }
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetValidationTemplate"/>
        public ValidationTemplate GetValidationTemplate(int templateID)
        {
            String filter = DBConstants.VALIDATION_TEMPLATES_ID + "=" + templateID;
            List<ValidationTemplate> templates = GetValidationTemplates(filter);
            //assuming 1 or 0 results only
            if (templates.Count == 0)
            {
                return null;
            }
            else
            {
                return templates.ToArray()[0];
            }
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetAllUserQueries"/>
        public List<UserQuery> GetAllUserQueries(String userID)
        {
            String filter = DBConstants.QUERIES_USERID + "='" + userID + "'";
            List<UserQuery> queries = GetQueries(filter);
            return queries;
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetTestParams(int, int)"/>
        public TestParams GetTestParams(String testInfoIndex, int testParamsIndex)
        {
            String filter = DBConstants.TEST_PARAMS_TEST_INFO_INDEX + "='" + testInfoIndex + "' AND " + DBConstants.TEST_PARAMS_INDEX + "=" + testParamsIndex;
            List<TestParams> testParams = GetTestParams(filter);
            //assuming 1 or 0 results only
            if (testParams.Count == 0)
            {
                return null;
            }
            else
            {
                return testParams.ToArray()[0];
            }
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetTestResults(int, int, int)"/>
        public TestResults GetTestResults(String testInfoIndex, int testParamsIndex, int attenuation)
        {
            String filter = DBConstants.TEST_RESULTS_TEST_INFO_INDEX + "='" + testInfoIndex + "' AND " + DBConstants.TEST_RESULTS_TEST_PARAMS_INDEX + "=" + 
                testParamsIndex + " AND " + DBConstants.TEST_RESULTS_ATTENUATION + "=" + attenuation;
            List<TestResults> testResults = GetTestResults(filter);
            //assuming 1 or 0 results only
            if (testResults.Count == 0)
            {
                return null;
            }
            else
            {
                return testResults.ToArray()[0];
            }
        }
        /// <see cref="Danser_Control.Managers.IDBManager.GetTestData"/>
        public IList<TestIntegratedData> GetTestData(String filter)
        {
            String query = "SELECT " + DBConstants.TEST_INFO_INDEX + "," + DBConstants.TEST_PARAMS_INDEX + "," + DBConstants.TEST_RESULTS_ATTENUATION + " FROM " +
                DBConstants.TESTS_VIEW_NAME + " WHERE " + filter;
            SqlDataReader reader = PerformQuery(query);
            List<String> testInfoIds = new List<String>();
            List<int> testParamsIds = new List<int>();
            List<int> attenIds = new List<int>();

            List<TestIntegratedData> lstData = new List<TestIntegratedData>();
            if (reader == null)
            {
                return lstData;
            }
            while (reader.Read())
            {
                String testInfoIndex = (String)reader[DBConstants.TEST_INFO_INDEX];
                int testParamsIndex = (int)reader[DBConstants.TEST_PARAMS_INDEX];
                int attenuation = (int)reader[DBConstants.TEST_RESULTS_ATTENUATION];
                testInfoIds.Add(testInfoIndex);
                testParamsIds.Add(testParamsIndex);
                attenIds.Add(attenuation);
            }

            reader.Close();

            String[] testInfos = testInfoIds.ToArray();
            int[] testParams = testParamsIds.ToArray();
            int[] attens = attenIds.ToArray();

            for (int i = 0; i < testInfos.Length; i++)
            {
                TestParams tParams = GetTestParams(testInfos[i], testParams[i]);
                TestResults tResults = GetTestResults(testInfos[i], testParams[i], attens[i]);
                if (tParams != null && tResults != null)
                {
                    TestIntegratedData data = new TestIntegratedData(tParams, tResults);
                    lstData.Add(data);
                }
            }
            return lstData;
        }


        #endregion
                    
        #region Table Queries

        /// <see cref="Danser_Control.Managers.IDBManager.GetValidationTemplates"/>
        public List<ValidationTemplate> GetValidationTemplates(String filter)
        {
            String query = CreateSelectQuery(DBConstants.VALIDATION_TEMPLATES_TABLE,filter);
            SqlDataReader reader = PerformQuery(query);
            List<ValidationTemplate> templates = new List<ValidationTemplate>();
            if (reader == null)
            {
                return templates;
            }

            while (reader.Read())
            {
                ValidationTemplate template = CreateValidationTemplate(reader);
                templates.Add(template);
            }
            reader.Close();
            return templates;
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetUsers"/>
        public List<User> GetUsers(String filter)
        {
            String query = CreateSelectQuery(DBConstants.USERS_TABLE, filter);
            SqlDataReader reader = PerformQuery(query);
            List<User> users = new List<User>();
            if (reader == null)
            {
                return users;
            }

            while (reader.Read())
            {
                User user = CreateUser(reader);
                users.Add(user);
            }
            reader.Close();
            return users;
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetQueries"/>
        public List<UserQuery> GetQueries(String filter)
        {
            String query = CreateSelectQuery(DBConstants.QUERIES_TABLE, filter);
            SqlDataReader reader = PerformQuery(query);
            List<UserQuery> queries = new List<UserQuery>();
            if (reader == null)
            {
                return queries;
            }

            while (reader.Read())
            {
                UserQuery userQuery = CreateUserQuery(reader);
                queries.Add(userQuery);
            }
            reader.Close();
            return queries;
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetTestResults(String)"/>
        public List<TestResults> GetTestResults(String filter)
        {
            String query = CreateSelectQuery(DBConstants.TEST_RESULTS_TABLE, filter);
            SqlDataReader reader = PerformQuery(query);
            List<TestResults> testResults = new List<TestResults>();
            if (reader == null)
            {
                return testResults;
            }

            while (reader.Read())
            {
                TestResults testResult = CreateTestResults(reader);
                testResults.Add(testResult);
            }
            reader.Close();
            return testResults;
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetTestParams(String)"/>
        public List<TestParams> GetTestParams(String filter)
        {
            String query = CreateSelectQuery(DBConstants.TEST_PARAMS_TABLE, filter);
            SqlDataReader reader = PerformQuery(query);
            List<TestParams> testParams = new List<TestParams>();
            if (reader == null)
            {
                return testParams;
            }

            while (reader.Read())
            {
                TestParams testParam = CreateTestParams(reader);
                testParams.Add(testParam);
            }
            reader.Close();
            return testParams;
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetTestInfo"/>
        public List<TestInfo> GetTestInfo(String filter)
        {
            String query = CreateSelectQuery(DBConstants.TEST_INFO_TABLE, filter);
            SqlDataReader reader = PerformQuery(query);
            List<TestInfo> testInfos = new List<TestInfo>();
            if (reader == null)
            {
                return testInfos;
            }

            while (reader.Read())
            {
                TestInfo testInfo = CreateTestInfo(reader);
                testInfos.Add(testInfo);
            }
            reader.Close();
            return testInfos;
        }

        #endregion

        #region Validation methogs

        /// <see cref="Danser_Control.Managers.IDBManager.ValidateTest"/>
        public bool ValidateTest(String testInfoIndex, String validationCondition)
        {
            String sqlCondition = Utils.String2SQLExpression(validationCondition);
            int totalTestResults = GetTestsCountFromView(DBConstants.TEST_RESULTS_TEST_INFO_INDEX + "='" + testInfoIndex + "'");
            int passedTestResults = GetTestsCountFromView(DBConstants.TEST_RESULTS_TEST_INFO_INDEX + "='" + testInfoIndex + "' AND (" + sqlCondition + ")");
            return (totalTestResults - passedTestResults == 0);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.SetFailedTest"/>
        public void SetFailedTest(int validationTemplateID, String testInfoIndex)
        {
            String cmd = "EXECUTE usp_setFailedTest " + validationTemplateID + ", '" + testInfoIndex + "'";
            ExecSP(cmd);
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetFailedTestForTemplate"/>
        public List<TestInfo> GetFailedTestForTemplate(int validationTemplateID)
        {
            String query = CreateSelectQuery(DBConstants.VALIDATION_FAILURES_TABLE, DBConstants.VALIDATION_FAILURES_TEMPLATE_ID + "=" + validationTemplateID);
            List<String> testIDs = GetIDsList<String>(query, DBConstants.VALIDATION_FAILURES_TEST_INFO_ID);
            List<TestInfo> failedTests = new List<TestInfo>();
            foreach (String id in testIDs)
            {
                TestInfo test = GetTest(id);
                if (test != null)
                {
                    failedTests.Add(test);
                }
            }
            return failedTests;
        }

        /// <see cref="Danser_Control.Managers.IDBManager.GetFailingTemplatesForTest"/>
        public List<ValidationTemplate> GetFailingTemplatesForTest(String testInfoIndex)
        {
            String query = CreateSelectQuery(DBConstants.VALIDATION_FAILURES_TABLE, DBConstants.VALIDATION_FAILURES_TEST_INFO_ID + "='" + testInfoIndex + "'");
            List<int> templateIDs = GetIDsList<int>(query, DBConstants.VALIDATION_FAILURES_TEMPLATE_ID);
            List<ValidationTemplate> templates = new List<ValidationTemplate>();
            foreach (int id in templateIDs)
            {
                ValidationTemplate template = GetValidationTemplate(id);
                if (template != null)
                {
                    templates.Add(template);
                }
            }

            return templates;
        }
        #endregion

        /* Execute a non query (i.e. insert/delete) stored procedure */
        private void ExecSP(String s)
        {
            SqlCommand cmd = GetConnection().CreateCommand();
            cmd.CommandText = s;
            cmd.ExecuteNonQuery();
        }

        private bool IsStringEmpty(String s)
        {
            return s == null || s.Length == 0;
        }

        #region Entities creators

        private User CreateUser(SqlDataReader reader)
        {
            String id = (String)reader[DBConstants.USERS_ID];
            String name = (String)reader[DBConstants.USERS_NAME];
            String permission = (String)reader[DBConstants.USERS_PERMISSION];

            User user = new User();
            user.UserID = id;
            user.Name = name;
            user.Permission = GetPermission(permission);
            return user;
        }

        private TestInfo CreateTestInfo(SqlDataReader reader)
        {
            String testInfoIndex = (String)reader[DBConstants.TEST_INFO_INDEX];
            String sOperator = (String)reader[DBConstants.TEST_INFO_OPERATOR];
            String date = (String)reader[DBConstants.TEST_INFO_DATE];
            String time = (String)reader[DBConstants.TEST_INFO_TIME];
            String testName = (String)reader[DBConstants.TEST_INFO_TEST_NAME];
            String apType = (String)reader[DBConstants.TEST_INFO_APTYPE];
            String apip = (String)reader[DBConstants.TEST_INFO_APIP];
            String apfwver = (String)reader[DBConstants.TEST_INFO_APFWVER];
            String nicmac = (String)reader[DBConstants.TEST_INFO_NICMAC];
            String nictype = (String)reader[DBConstants.TEST_INFO_NICTYPE];
            String projectName = (String)reader[DBConstants.TEST_INFO_PROJ_NAME];
            String os = (String)reader[DBConstants.TEST_INFO_OS];
            String tic = (String)reader[DBConstants.TEST_INFO_TIC];
            String setupName = (String)reader[DBConstants.TEST_INFO_SETUP_NAME];
            String platformModel = (String)reader[DBConstants.TEST_INFO_PLATFORM_MODEL];
            String platformName = (String)reader[DBConstants.TEST_INFO_PLATFORM_NAME];
            String trafficProtocol = (String)reader[DBConstants.TEST_INFO_TRAFFIC_PROTOCOL];
            bool isConfirmed = Boolean.Parse(reader[DBConstants.TEST_INFO_IS_CONFIRMED].ToString());
            bool isAnchor = Boolean.Parse(reader[DBConstants.TEST_INFO_IS_ANCHOR].ToString());

            TestInfo testInfo = new TestInfo();
            testInfo.TestInfoIndex = testInfoIndex;
            testInfo.Operator = sOperator;
            testInfo.Date = date;
            testInfo.Time = time;
            testInfo.TestName = testName;
            testInfo.APType = apType;
            testInfo.APIP = apip;
            testInfo.APFWVer = apfwver;
            testInfo.NICMAC = nicmac;
            testInfo.NICType = nictype;
            testInfo.ProjectName = projectName;
            testInfo.OS = os;
            testInfo.TIC = tic;
            testInfo.SetupName = setupName;
            testInfo.PlatformModel = platformModel;
            testInfo.PlatformName = platformName;
            testInfo.TrafficProtocol = trafficProtocol;
            testInfo.IsConfirmed = isConfirmed;
            testInfo.IsAnchor = isAnchor;

            return testInfo;
        }

        private UserQuery CreateUserQuery(SqlDataReader reader)
        {
            int queryID = (int)reader[DBConstants.QUERIES_ID];
            String queryName = (String)reader[DBConstants.QUERIES_NAME];
            String description = (String)reader[DBConstants.QUERIES_DESCRIPTION];
            String queryString = (String)reader[DBConstants.QUERIES_STRING];
            String userid = (String)reader[DBConstants.QUERIES_USERID];

            UserQuery query = new UserQuery();
            query.ID = queryID;
            query.Name = queryName;
            query.Description = description;
            query.QueryString = queryString;
            query.UserID = userid;

            return query;
        }

        private ValidationTemplate CreateValidationTemplate(SqlDataReader reader)
        {
            int id = (int)reader[DBConstants.VALIDATION_TEMPLATES_ID];
            String name = (String)reader[DBConstants.VALIDATION_TEMPLATES_NAME];
            bool appliedOnDefault = Boolean.Parse(reader[DBConstants.VALIDATION_TEMPLATES_APPLIED_ON_DEFAULT].ToString());
            String mails = (String)reader[DBConstants.VALIDATION_TEMPLATES_MAIL_LIST];
            String expression = (String)reader[DBConstants.VALIDATION_TEMPLATES_EXPRESSION];

            //List<String> mailList = new List<string>(mails.Split(new Char[] { ',' }));

            ValidationTemplate template = new ValidationTemplate();
            template.ID = id;
            template.Name = name;
            template.AppliedOnDefault = appliedOnDefault;
            template.MailList = mails;
            template.Expression = expression;

            return template;
        }

        private TestResults CreateTestResults(SqlDataReader reader)
        {
            double rxMin = (double)reader[DBConstants.TEST_RESULTS_RXMIN];
            double rxMax = (double)reader[DBConstants.TEST_RESULTS_RXMAX];
            double rxAvg = (double)reader[DBConstants.TEST_RESULTS_RXAVG];
            double txMin = (double)reader[DBConstants.TEST_RESULTS_TXMIN];
            double txMax = (double)reader[DBConstants.TEST_RESULTS_TXMAX];
            double txAvg = (double)reader[DBConstants.TEST_RESULTS_TXAVG];
            double rssi1 = (double)reader[DBConstants.TEST_RESULTS_RSSI1];
            double rssi2 = (double)reader[DBConstants.TEST_RESULTS_RSSI2];
            double rssi3 = (double)reader[DBConstants.TEST_RESULTS_RSSI3];
            String testInfoIndex = (String)reader[DBConstants.TEST_RESULTS_TEST_INFO_INDEX];
            int testParamsIndex = (int)reader[DBConstants.TEST_RESULTS_TEST_PARAMS_INDEX];
            int attenuation = (int)reader[DBConstants.TEST_RESULTS_ATTENUATION];

            TestResults testResults = new TestResults();
            testResults.RXMin = rxMin;
            testResults.RXMax = rxMax;
            testResults.RXAvg = rxAvg;
            testResults.TXMin = txMin;
            testResults.TXMax = txMax;
            testResults.TXAvg = txAvg;
            testResults.RSSI1 = rssi1;
            testResults.RSSI2 = rssi2;
            testResults.RSSI3 = rssi3;
            testResults.TestInfoIndex = testInfoIndex;
            testResults.TestParamsIndex = testParamsIndex;
            testResults.Attenuation = attenuation;

            return testResults;
        }

        private TestParams CreateTestParams(SqlDataReader reader)
        {
            int testParamsIndex = (int)reader[DBConstants.TEST_PARAMS_INDEX];
            String channel = (String)reader[DBConstants.TEST_PARAMS_CHANNEL];
            String channelWidth = (String)reader[DBConstants.TEST_PARAMS_CHANNEL_WIDTH];
            String band = (String)reader[DBConstants.TEST_PARAMS_BAND];
            String testInfoIndex = (String)reader[DBConstants.TEST_PARAMS_TEST_INFO_INDEX];

            TestParams testParams = new TestParams();
            testParams.TestParamsIndex = testParamsIndex;
            testParams.Channel = channel;
            testParams.ChannelWidth = channelWidth;
            testParams.Band = band;
            testParams.TestInfoIndex = testInfoIndex;

            return testParams;
        }

        #endregion

        private PermissionLevel GetPermission(String permission)
        {
            switch (permission)
            {
                case "Viewer": return PermissionLevel.Viewer;
                case "Editor": return PermissionLevel.Editor;
                case "Manager": return PermissionLevel.Manager;
                case "Admin": return PermissionLevel.Admin;
                default: return PermissionLevel.Viewer;
            }
        }

        private String List2String(IList<String> lst)
        {
            String result = "";
            foreach (String s in lst)
            {
                result += s + ",";
            }
            result = result.Remove(result.Length-1);
            return result;
        }

        private String CreateSelectQuery(String tableName, String filter)
        {
            String query = "SELECT * FROM " + tableName;
            if (!IsStringEmpty(filter))
            {
                query += " WHERE " + filter;
            }
            return query;
        }

        private int GetTestsCountFromView(String filter)
        {
            String query = CreateSelectQuery(DBConstants.TESTS_VIEW_NAME, filter);
            SqlDataReader reader = PerformQuery(query);
            if (reader == null)
            {
                return 0;
            }
            int counter = 0;
            while (reader.Read())
            {
                counter++;
            }
            reader.Close();
            return counter;
        }

        private List<T> GetIDsList<T>(String sqlQuery, String fieldName)
        {
            List<T> IDs = new List<T>();

            SqlDataReader reader = PerformQuery(sqlQuery);
            if (reader == null)
            {
                return IDs;
            }
            while (reader.Read())
            {
                T ID = (T)reader[fieldName];
                IDs.Add(ID);
            }
            reader.Close();
            return IDs;
        }
          


    }
}
